/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cheapflix.dao;

import com.cheapflix.beans.DVD;
import com.cheapflix.beans.RecommendedDVD;
import com.cheapflix.beans.User;
import com.cheapflix.beans.UserDVD;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * DAO class in charge of User level functionalities
 * @author Group B
 */
public class UserDAO extends AbstractDAO {

    private static final Logger logger = Logger.getLogger(UserDAO.class);

    //default constructor to make Spring happy :)
    public UserDAO() {
    }

    @Autowired
    public UserDAO(DataSource ds) {
        super.init(ds);
    }

    /**
     * Marshaller method, marshalls a result set to a  RecommendedDVD DTO 
     * @param rs
     * @return
     * @throws SQLException 
     */
    private static RecommendedDVD getRecommendedDVD(ResultSet rs) throws SQLException {
        if (rs == null) {
            return null;
        }
        int id = rs.getInt("id");
        int reccomendorID = rs.getInt("reccomendor_id");
        int reccomendeeID = rs.getInt("reccomendee_id");
        int dvdId = rs.getInt("dvd_id");
        RecommendedDVD rdvd = new RecommendedDVD();
        rdvd.setId(id);
        rdvd.setDvdId(dvdId);
        rdvd.setRecommendeeId(reccomendeeID);
        rdvd.setRecommendorId(reccomendorID);
        return rdvd;
    }

    /**
     * Marshaller method, marshalls a resultset to User DTO.
     * @param rs
     * @return
     * @throws SQLException 
     */
    public static User getUser(ResultSet rs) throws SQLException {
        if (rs == null) {
            return null;
        }
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setEmail(rs.getString("email"));
        user.setNameFirst(rs.getString("nameFirst"));
        user.setNameLast(rs.getString("nameLast"));
        //probably will be md5 checksum of password....
        user.setPassword(rs.getString("password"));
        user.setUsername(rs.getString("username"));
        return user;
    }

    /**
     * Marshaller method, marshalls resultset to DVD.
     */
    private static DVD getDVD(ResultSet rs) throws SQLException {
        if (rs == null) {
            return null;
        }
        DVD dvd = new DVD();
        dvd.setId(rs.getInt("id"));
        dvd.setActors(rs.getString("actors"));
        dvd.setDirector(rs.getString("director"));
        dvd.setGenre(rs.getString("genre"));
        dvd.setImage(rs.getString("image"));
        dvd.setRuntime(rs.getString("runtime"));
        dvd.setTitle(rs.getString("title"));
        dvd.setYear(rs.getString("year"));
        return dvd;
    }

    private static UserDVD getUserDVD(ResultSet rs) throws SQLException {
        if (rs == null) {
            return null;
        }
        UserDVD uDvd = new UserDVD();
        uDvd.setDvdId(rs.getInt("dvd_id"));
        uDvd.setId(rs.getInt("id"));
        uDvd.setRating(rs.getInt("rating"));
        uDvd.setUserId(rs.getInt("user_id"));
        uDvd.setComment(rs.getString("comment"));
        return uDvd;
    }

    /**
     * add a user to system.
     * @param user
     * @return 
     */
    public boolean addUser(User user) {
        String sql = "insert into user (username, password, email, nameFirst, nameLast) values ('%s',md5('%s'),'%s','%s','%s')";
        sql = String.format(sql, user.getUsername(), user.getPassword(), user.getEmail(), user.getNameFirst(), user.getNameLast());
        Connection conn = null;
        Statement stmt;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info(">sql: " + sql);
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return false;
        }
        return true;
    }

    /**
     * get user from system by pk
     */
    public User getUser(int userId) {
        String sql = "select * from user where id=" + userId;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info("> sql: " + sql);
            rs = stmt.executeQuery(sql);
            if (!rs.next()) {
                return null;
            }
            User user = getUser(rs);
            if (rs != null) {
                rs.close();
            }
            sql = "select a.* from user a, flixfriends b where b.user_id=" + user.getId() + " and a.id=b.friend_id";
            logger.info(">sql: " + sql);
            rs = stmt.executeQuery(sql);
            HashMap< String, User> flixFriends = new HashMap< String, User>();

            while (rs.next()) {
                User friend = getUser(rs);

                flixFriends.put(rs.getInt("id") + "", friend);

            }
            logger.info("Flix friends: " + flixFriends);
            user.setFriends(flixFriends);
            if (rs != null) {
                rs.close();
            }
            sql = "select * from user_dvd_reccomend where reccomendor_id=" + user.getId();
            HashMap<String, List<RecommendedDVD>> recommendedDVDs = new HashMap<String, List< RecommendedDVD>>();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                RecommendedDVD rdvd = getRecommendedDVD(rs);
                if (recommendedDVDs.get(rs.getInt("dvd_id") + "") == null) {
                    List<RecommendedDVD> newlist = new ArrayList<RecommendedDVD>();
                    recommendedDVDs.put(rs.getInt("dvd_id") + "", newlist);
                }
                List<RecommendedDVD> rlist = recommendedDVDs.get(rs.getInt("dvd_id") + "");

                rlist.add(rdvd);
            }
            user.setRecommendedDVDs(recommendedDVDs);
            if (rs != null) {
                rs.close();
            }
            //get user's flick queue 
            UserDVDDao udvddao = new UserDVDDao();
            udvddao.init(getDataSource());

            List<UserDVD> userDvdList = udvddao.getUserDVDQueue(user.getId());
            for (UserDVD udvd : userDvdList) {
                user.addUserDVD(udvd.getDvdId() + "", udvd);
            }

            return user;
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        } finally {
            close(conn, stmt, rs);
        }
    }

    /**
     * get user by username/password
     * @param username
     * @param password
     * @return 
     */
    public User getUser(String username, String password) {
        String sql = "select * from user where username='" + username + "'"
                + "and password=md5('" + password + "')";


        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info("> sql: " + sql);
            rs = stmt.executeQuery(sql);
            if (!rs.next()) {
                return null;
            }
            User user = getUser(rs);
            if (rs != null) {
                rs.close();
            }
            sql = "select a.* from user a, flixfriends b where b.user_id=" + user.getId() + " and a.id=b.friend_id";
            logger.info(">sql: " + sql);
            rs = stmt.executeQuery(sql);
            HashMap< String, User> flixFriends = new HashMap< String, User>();

            while (rs.next()) {
                User friend = getUser(rs);

                flixFriends.put(rs.getInt("id") + "", friend);

            }
            logger.info("Flix friends: " + flixFriends);
            user.setFriends(flixFriends);
            if (rs != null) {
                rs.close();
            }
            sql = "select * from user_dvd_reccomend where reccomendor_id=" + user.getId();
            HashMap<String, List<RecommendedDVD>> recommendedDVDs = new HashMap<String, List< RecommendedDVD>>();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                RecommendedDVD rdvd = getRecommendedDVD(rs);
                if (recommendedDVDs.get(rs.getInt("dvd_id") + "") == null) {
                    List<RecommendedDVD> newlist = new ArrayList<RecommendedDVD>();
                    recommendedDVDs.put(rs.getInt("dvd_id") + "", newlist);
                }
                List<RecommendedDVD> rlist = recommendedDVDs.get(rs.getInt("dvd_id") + "");

                rlist.add(rdvd);
            }
            user.setRecommendedDVDs(recommendedDVDs);
            if (rs != null) {
                rs.close();
            }
            //get user's flick queue 
            UserDVDDao udvddao = new UserDVDDao();
            udvddao.init(getDataSource());

            List<UserDVD> userDvdList = udvddao.getUserDVDQueue(user.getId());
            for (UserDVD udvd : userDvdList) {
                user.addUserDVD(udvd.getDvdId() + "", udvd);
            }

            return user;
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        } finally {
            close(conn, stmt, rs);
        }
    }

    /**
     * reomove a friend from system.
     * @param myUserID
     * @param friendID
     * @return 
     */
    public boolean removeFriend(int myUserID, int friendID) {
        String sql = "delete from flixfriends where user_id = " + myUserID + " and friend_id = " + friendID;
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info("> sql: " + sql);
            int updatedRows = stmt.executeUpdate(sql);
            if (updatedRows == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return false;
        } finally {
            close(conn, stmt, null);
        }
    }

    /**
     * get a user's flick queue.
     * @param UserID
     * @return 
     */
    public ArrayList<DVD> getQueue(int UserID) {
        ArrayList<DVD> queueDVDs = new ArrayList<DVD>();

        String sql = "";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        DVD dvd = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            sql = "select b.* from user_dvd a, dvd b where a.dvd_id=b.id and a.user_id =" + UserID + " order by rating, title";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                dvd = getDVD(rs);
                if (dvd != null) {
                    queueDVDs.add(dvd);
                }
            }

            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return queueDVDs;
        } finally {
            close(conn, stmt, rs);
        }
        return queueDVDs;

    }

    /**
     * get a user's flick queue by user DTO.
     * @param user
     * @return 
     */
    public ArrayList<DVD> getQueue(User user) {
        return (getQueue(user.getId()));
    }

    /**
     * search for users.
     * @param searchString
     * @return 
     */
    public Set<User> SearchUsers(String searchString) {
        Set<User> FoundUsers = new HashSet<User>();
        //search by username
        List<User> ByUsername = SearchUsersByColumn("username", searchString);
       
        if (ByUsername != null) {
            //  FoundUsers.put("username", ByUsername);
            FoundUsers.addAll(ByUsername);
        }
        //search by first name
        List<User> ByFirstName = SearchUsersByColumn("nameFirst", searchString);
        if (ByFirstName != null) {
            FoundUsers.addAll(ByFirstName);
        }
        //by last name
        List<User> ByLastName = SearchUsersByColumn("nameLast", searchString);
        if (ByLastName != null) {
           
            FoundUsers.addAll(ByLastName);
        }
        logger.info("FoundUsers: " + FoundUsers);
        return FoundUsers;
    }

    /**
     * utility method for user search
     */
    public List<User> SearchUsersByColumn(String column, String searchString) {
        if (searchString.length() > 0) {
            List<User> users = new ArrayList<User>();
            String sql = "select * from user where " + column + " like '%" + searchString + "%'";
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                conn = getDataSource().getConnection();
                logger.info(">sql: " + sql);
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    users.add(getUser(rs));
                }
                return users;
            } catch (SQLException ex) {
                logger.error(ex.getMessage(), ex);
                return null;
            } finally {
                close(conn, stmt, rs);
            }
        } else {
            return null;
        }

    }

    /**
     * Given a primary user's ID (me) add a friend to my list of friends.
     * @param myId primary user id (ME)
     * @param friendId ID of other person to friend
     * @return  true/false as to whether friending operation was successfull.
     */
    public boolean addAsFriend(int myId, int friendId) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getDataSource().getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into flixfriends ( user_id, friend_id ) values (" + myId + "," + friendId + " ) ";
            stmt = conn.createStatement();
            logger.info("> sql: " + sql);
            int numUpdated = stmt.executeUpdate(sql);
            if (numUpdated == 1) {
                conn.commit();
                return true;
            } else {
                conn.rollback();
                return false;
            }

        } catch (SQLException ex) {
            try {
                conn.rollback();
            } catch (SQLException ex1) {
                //swallow
            }
            logger.error(ex.getMessage(), ex);
            return false;
        } finally {
            close(conn, stmt, null);
        }
    }

    /**
     * update a user's information
     * @param id primary key of user to update info of
     * @param nameFirst possibly new first name of user
     * @param nameLast possibly new last name of user
     * @param password possibly new password of user.
     * @return  true/false whether update op was successfull
     */
    public boolean updateUserInfo(int id, String nameFirst, String nameLast, String password) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getDataSource().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set nameFirst = '" + nameFirst + "', nameLast='" + nameLast + "', password=md5('" + password + "')";
            stmt = conn.createStatement();
            logger.info("> sql: " + sql);
            int numUpdated = stmt.executeUpdate(sql);
            if (numUpdated == 1) {
                conn.commit();
                return true;
            } else {
                conn.rollback();
                return false;
            }

        } catch (SQLException ex) {
            try {
                conn.rollback();
            } catch (SQLException ex1) {
                //swallow
            }
            logger.error(ex.getMessage(), ex);
            return false;
        } finally {
            close(conn, stmt, null);
        }
    }
}
